/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package utilities;

import connection.ManLabConnection;
import entities.Admin;
import entities.BillingDetails;
import entities.BillingTestDetail;
import entities.DoctorDetails;
import entities.PatientDetails;
import entities.ReportDetails;
import entities.StaffDetail;
import entities.StockDetail;
import entities.Test;
import entities.TestDetail;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author VietLH
 */
public class AllEntitiesManage {
    
    private ArrayList<StockDetail> stockList;
    private ArrayList<TestDetail> testDetailList;
    private ArrayList<Test> testList;
    private ArrayList<BillingDetails> billList;
    private ArrayList<DoctorDetails> doctorList;
    private ArrayList<StaffDetail> staffList;
    private ArrayList<PatientDetails> patientList;
    private ArrayList<ReportDetails> reportList;
    private Admin admin;
    private ManLabConnection cnn;
    private ResultSet rs;
    private String qry;
    
    public AllEntitiesManage() {
        
    }

    private void createConnection(){
        cnn = new ManLabConnection();
        cnn.createConnection();
    }
    private void closeConnection(){
        cnn.closeConnection();
    }

    public ArrayList<DoctorDetails> getDoctorList() {
        createConnection();
        createDoctorList();
        closeConnection();
        return doctorList;
    }

    public ArrayList<PatientDetails> getPatientList() {
        createConnection();
        createPatientList();
        closeConnection();
        return patientList;
    }

    public ArrayList<ReportDetails> getReportList() {
        createConnection();
        createDoctorList();
        createPatientList();
        createStaffList();
        createTestList();
        createReportList();
        closeConnection();
        return reportList;
    }

    public ArrayList<StaffDetail> getStaffList() {
        createConnection();
        createStaffList();
        closeConnection();
        return staffList;
    }

    public ArrayList<StockDetail> getStockList() {
        createConnection();
        createStockList();
        closeConnection();
        return stockList;
    }

    public ArrayList<TestDetail> getTestDetailList() {
        createConnection();
        createTestList();
        createStockList();
        createTestDetailList();
        closeConnection();
        return testDetailList;
    }

    public ArrayList<Test> getTestList() {
        createConnection();
        createTestList();
        closeConnection();
        return testList;
    }
    
    public Admin getAdmin(){
        createConnection();
        createAdmin();
        closeConnection();
        return admin;
    }
            
    private void createPatientList() {
        try {
            patientList = new ArrayList<PatientDetails>();
            qry = "select * from PatientDetail";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                PatientDetails pd = new PatientDetails();
                pd.setpAddress(rs.getString("pAddress"));
                pd.setpDOB(rs.getDate("pDOB"));
                pd.setpFName(rs.getString("pFName"));
                pd.setpGender(rs.getBoolean("pGender"));
                pd.setpID(rs.getInt("pID"));
                pd.setpLName(rs.getString("pLName"));
                pd.setpPhone(rs.getString("pPhone"));
                patientList.add(pd);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    private void createStaffList() {
        try {
            staffList = new ArrayList<StaffDetail>();
            qry = "select * from StaffDetail";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                StaffDetail std = new StaffDetail();
                std.setStaffAddress(rs.getString("staffAddress"));
                std.setStaffDOB(rs.getDate("staffDOB"));
                std.setStaffDOJ(rs.getDate("staffDOJ"));
                std.setStaffEduQual(rs.getString("staffEduQual"));
                std.setStaffFName(rs.getString("staffFName"));
                std.setStaffGender(rs.getBoolean("staffGender"));
                std.setStaffID(rs.getInt("staffID"));
                std.setStaffLName(rs.getString("staffLName"));
                std.setStaffPassword(rs.getString("staffPassword"));
                std.setStaffPhone(rs.getString("staffPhone"));
                std.setStaffTechSkill(rs.getString("staffTechSkill"));
                std.setStaffUsername(rs.getString("staffUsername"));
                staffList.add(std);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    private void createDoctorList() {
        try {
            doctorList = new ArrayList<DoctorDetails>();
            qry = "select * from DoctorDetail";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                DoctorDetails dd = new DoctorDetails();
                dd.setdID(rs.getInt("dID"));
                dd.setdFName(rs.getString("dFName"));
                dd.setdLName(rs.getString("dLName"));
                dd.setdAddress(rs.getString("dAddress"));
                dd.setdPhone(rs.getString("dPhone"));
                dd.setdSpecialization(rs.getString("dSpecialization"));
                dd.setPassword(rs.getString("dPassword"));
                dd.setdUsername(rs.getString("dUsername"));
                dd.setdGender(rs.getBoolean("dGender"));
                doctorList.add(dd);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }



    private void createStockList() {
        try {
            stockList = new ArrayList<StockDetail>();
            
            qry = "select * from StockDetail";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                StockDetail stock = new StockDetail();
                stock.setsID(rs.getInt("sID"));
                stock.setsName(rs.getString("sName"));
                stock.setsAvailable(rs.getInt("sAvailable"));
                stock.setIsReuseable(rs.getBoolean("sReuseable"));
                stockList.add(stock);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    private void createTestList() {
        try {
            testList = new ArrayList<Test>();
            qry = "select * from Test";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                Test t = new Test();
                t.setTestID(rs.getInt("testID"));
                t.setTestName(rs.getString("testName"));
                t.setTestCost(rs.getFloat("testCost"));
                testList.add(t);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void createTestDetailList() {
        try {
            testDetailList = new ArrayList<TestDetail>();
            qry = "select distinct(testID) from ItemRequire";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                TestDetail td = new TestDetail();
                Test t = new Test();
                for (Test test : testList) {
                    if (test.getTestID() == rs.getInt("testID")) {
                        t = test;
                        break;
                    }
                }
                td.setTest(t);
                testDetailList.add(td);
            }
            for (TestDetail testDetail : testDetailList) {
                qry = "select sID, iNoRequire from ItemRequire where testID=" + testDetail.getTest().getTestID();
                rs = cnn.createResultSet(qry);
                while (rs.next()) {
                    StockDetail sd = new StockDetail();
                    for (StockDetail stockDetail : stockList) {
                        if (stockDetail.getsID() == rs.getInt("sID")) {
                            sd = stockDetail;
                        }
                    }
                    testDetail.addStockDetail(sd);
                    testDetail.setNumberItemRequire(sd, rs.getInt("iNoRequire"));
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void createReportList() {
        try {
            reportList = new ArrayList<ReportDetails>();
            qry = "select * from Report";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                ReportDetails rd = new ReportDetails();
                rd.setReportID(rs.getInt("reportID"));
                rd.setTestDate(rs.getDate("testDate"));
                rd.setReport(rs.getString("report"));
                rd.setStatus(rs.getInt("status"));
                for (DoctorDetails doctorDetail : doctorList) {
                    if (doctorDetail.getdID() == rs.getInt("dID")) {
                        rd.setDoctorDetails(doctorDetail);
                        break;
                    }
                }
                for (Test test : testList) {
                    if (test.getTestID() == rs.getInt("testID")) {
                        rd.setTest(test);
                        break;
                    }                    
                }
                for (StaffDetail staffDetail : staffList) {
                    if (staffDetail.getStaffID() == rs.getInt("staffID")) {
                        rd.setStaffDetail(staffDetail);
                        break;
                    }
                }
                for (PatientDetails patientDetails : patientList) {
                    if (patientDetails.getpID() == rs.getInt("pID")) {
                        rd.setPatientDetails(patientDetails);
                        break;
                    }
                }
                reportList.add(rd);
            }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void createBillList() {
        try {
            billList = new ArrayList<BillingDetails>();
            qry = "select * from Billing";
            rs = cnn.createResultSet(qry);
            while (rs.next()) {
                BillingDetails bd=new BillingDetails();

                for (PatientDetails patient : patientList) {
                    if(patient.getpID()==rs.getInt("pID")){
                    bd.setPatientDetails(patient);
                    break;
                    }
                }
                bd.setbAmount(rs.getFloat("bAmount"));
                bd.setbDate(rs.getDate("bDate"));
                bd.setbID(rs.getInt("bID"));
                billList.add(bd);
            }
            for (BillingDetails billingDetail : billList) {
                qry="select * from BillDetail where bID="+billingDetail.getbID();
                cnn.createResultSet(qry);
                while(rs.next()){
                    BillingTestDetail btd=new BillingTestDetail();
                    for (Test test : testList) {
                        if(test.getTestID()==rs.getInt("testID"))
                        {
                            btd.setTest(test);
                            break;
                        }
                    }
                    btd.setTestCost(rs.getFloat("testCost"));
                    billingDetail.addBillingTestDetail(btd);
                }
            }

        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }

    }
    private void createAdmin(){
        try {
            admin=new Admin();
            qry = "select * from Admin";
                rs = cnn.createResultSet(qry);
                while (rs.next()) {
                    admin.setaPassword(rs.getString("aPassword"));
                }
        } catch (SQLException ex) {
            Logger.getLogger(AllEntitiesManage.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

}
